package net.shop.dal.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import net.shop.dal.dao.ProductVirtualCategoryDao;
import net.shop.dal.entity.LangProductVirtualCategory;
import net.shop.dal.entity.ProductVirtualCategory;

@Repository
public class JdbcProductVirtualCategoryDao extends JdbcBaseDao<ProductVirtualCategory, Integer> implements ProductVirtualCategoryDao {
	
	private static final String COUNT_SQL = "SELECT COUNT(*) FROM web_product_virtual_category";
	private static final String INSERT_SQL = "INSERT INTO web_product_virtual_category(category_id,category_name,hase_code,parent_cid,is_parent,depth,really_cid,category_pic,brand_ids,friendly_url,view_name,list_order,status)VALUES(:categoryId,:categoryName,:haseCode,:parentCid,:isParent,:depth,:reallyCid,:categoryPic,:brandIds,:friendlyUrl,:viewName,:listOrder,:status)";
	private static final String MODIFY_SQL = "UPDATE web_product_virtual_category SET category_id=:categoryId,category_name=:categoryName,hase_code=:haseCode,parent_cid=:parentCid,is_parent=:isParent,depth=:depth,really_cid=:reallyCid,category_pic=:categoryPic,brand_ids=:brandIds,friendly_url=:friendlyUrl,view_name=:viewName,list_order=:listOrder,status=:status ";
	private static final String DELETE_SQL = "DELETE FROM web_product_virtual_category ";
	private static final String SELECT_SQL = "SELECT category_id,category_name,hase_code,parent_cid,is_parent,depth,really_cid,category_pic,brand_ids,friendly_url,view_name,list_order,status FROM web_product_virtual_category ";
	private static final String SELECT_JOIN_SQL = "SELECT c.category_id,c.category_name,c.hase_code,c.parent_cid,c.is_parent,c.depth,c.really_cid,c.category_pic,c.brand_ids,c.friendly_url,c.view_name,c.list_order,c.status,l.language_id,l.category_title FROM web_product_virtual_category AS c LEFT JOIN lang_product_virtual_category AS l ON l.language_id=? AND l.category_id=c.category_id ";

	private ParameterizedRowMapper<ProductVirtualCategory> mapper = new ParameterizedRowMapper<ProductVirtualCategory>() {
		public ProductVirtualCategory mapRow(ResultSet rs, int rowNum) throws SQLException {
			int size = rs.getMetaData().getColumnCount();
			ProductVirtualCategory category = new ProductVirtualCategory();
			LangProductVirtualCategory lang = new LangProductVirtualCategory();
			
			category.setCategoryId(rs.getInt(1));
			category.setCategoryName(rs.getString(2));
			category.setHaseCode(rs.getLong(3));
			category.setParentCid(rs.getInt(4));
			category.setIsParent(rs.getByte(5));
			category.setDepth(rs.getShort(6));
			category.setReallyCid(rs.getInt(7));
			category.setCategoryPic(rs.getString(8));
			category.setBrandIds(rs.getString(9));
			category.setFriendlyUrl(rs.getString(10));
			category.setViewName(rs.getString(11));
			category.setListOrder(rs.getShort(12));
			category.setStatus(rs.getByte(13));
			
			if (size > 13) {
				lang.setCategoryId(category.getCategoryId());
				lang.setLanguageId(rs.getShort(14));
				lang.setCategoryTitle(rs.getString(15));
			}
			category.setLang(lang);
			
			return category;
		}
	};
	
	public void save(ProductVirtualCategory category) {
		int cid = getMaxChildCategoryId(category.getParentCid());
		if (cid > 0) {
			category.setCategoryId(++cid);
		} else {
			category.setCategoryId(ProductVirtualCategory.START_NUMBER);
		}
		SqlParameterSource param = new BeanPropertySqlParameterSource(category);
		getSimpleJdbcTemplate().getNamedParameterJdbcOperations().update(INSERT_SQL, param);
	}
	
	public int delete(Integer categoryId) {
		String sql = DELETE_SQL +"WHERE category_id=?";
		return getSimpleJdbcTemplate().update(sql, categoryId);	
	}
	
	public int delete(ProductVirtualCategory category) {
		return delete(category.getCategoryId());
	}

	public int modify(ProductVirtualCategory category) {
		String sql = MODIFY_SQL +"WHERE category_id=:categoryId";
		return getSimpleJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(category));
	}

	public int getAllCount() {
		return getJdbcTemplate().queryForInt(COUNT_SQL);
	}

	public ProductVirtualCategory get(Integer categoryId) {
		String sql = SELECT_SQL +"WHERE category_id=?";
		Object[] args = new Object[]{categoryId};
		return (ProductVirtualCategory) getJdbcTemplate().queryForObject(sql, args, mapper);
	}	
	
	
	public ProductVirtualCategory getWithLang(short languageId, int categoryId) {
		String sql = SELECT_JOIN_SQL +"WHERE c.category_id=?";
		Object[] args = new Object[]{languageId, categoryId};
		return (ProductVirtualCategory) getJdbcTemplate().queryForObject(sql, args, mapper);
	}	

	public ProductVirtualCategory findByHaseCode(short languageId, long haseCode) {
		String sql = SELECT_JOIN_SQL +"WHERE c.hase_code=?";
		Object[] args = new Object[]{languageId, haseCode};
		return (ProductVirtualCategory) getJdbcTemplate().queryForObject(sql, args, mapper);		
	}

	public List<ProductVirtualCategory> getChildrenProductCategoryList(ProductVirtualCategory category) {
		String sql = SELECT_JOIN_SQL +"WHERE c.parent_cid=?";
		return getSimpleJdbcTemplate().query(sql, mapper, category.getLang().getLanguageId(), category.getCategoryId());
	}
	
	private int getMaxChildCategoryId(int categoryId) {
		String sql = "SELECT MAX(category_id) FROM web_product_virtual_category WHERE parent_cid=?";
		return getSimpleJdbcTemplate().queryForInt(sql, categoryId);
	}
}
